unit clsMovItemPedVenda;

interface

uses Dm, dbtables, SysUtils, biblio;

type
  TMovItemPedVenda = class(tobject)
  private
{$HINTS OFF}
    Ped_Numero: string;
    Ped_item: string;
    Nota_serie: string;
    Nota_numero: string;
    Ent_codigo: string;
    Itnv_item: string;
{$HINTS ON}

  public
    Ped_Numero2: string;
    V_Ped_Numero: string;
    V_Ped_item: string;
    V_Nota_serie: string;
    V_Nota_numero: string;
    V_Ent_codigo: string;
    V_Itnv_item: string;
    function Gravar(P_Ped_numero, P_Ped_Item, P_Nota_serie, P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
    function Recuperar(P_Ped_numero, P_Ped_Item, P_Nota_serie, P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
    function Recuperar1(var pedido1, Ped_Item: string; Ent_Codigo, Nota_Numero, Nota_Serie, Nota_Item: string): Boolean;
    function RecuperarNota(P_Nota_serie, P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
    procedure Recuperar_Nota(pedido, item: string);
    procedure Recuperar_Nota1(pedido, item: string);
    function Excluir(P_Nota_serie, P_nota_numero, p_nota_numero2, P_ent_codigo: string): byte;
    function Retorna_Pedido(P_Nota_serie, P_nota_numero, P_ent_codigo: string): string;
    function Retorna_PedidoItem(P_Nota_serie, P_nota_numero, P_ent_codigo: string): string;
    function Retorna_Pedidos(P_Nota_serie, P_nota_numero, P_ent_codigo: string): string;
    function Retorna_Pedido1(P_bkp_serie, P_bkp_numero, P_ent_codigo: string): string;
    function Retorna_QtdePedidos(P_Nota_serie, P_nota_numero, P_ent_codigo: string): Integer;

  end;

implementation

uses clsBackupVenda, DB, clsBackup_Pedido;

{ TMovItemPedVenda }

function TMovItemPedVenda.Excluir(P_Nota_serie, P_nota_numero, p_nota_numero2, P_ent_codigo: string): byte;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.close;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('delete from MovItemPedVenda where ' +
    'Nota_serie=:K_nota_serie and ' +
    '(Nota_numero=:K_Nota_numero or Nota_numero=:K_nota_numero2) and Ent_codigo=:K_ent_codigo');
  tq.ParamByName('K_nota_serie').AsString := P_Nota_serie;
  tq.ParamByName('K_Nota_numero').AsString := strzero(P_nota_numero, 9);
  tq.ParamByName('K_Nota_numero2').AsString := strzero(P_nota_numero2, 9);
  tq.ParamByName('K_ent_codigo').AsString := StrZero(P_ent_codigo, 5);
  tq.ExecSQL;
  result := 1;
  tq.close;
  tq.free;
end;

function TMovItemPedVenda.Gravar(P_Ped_numero, P_Ped_Item, P_Nota_serie,
  P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.close;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where ' +
    'Ped_numero=:K_Ped_Numero and Ped_item=:K_ped_item and Nota_serie=:K_nota_serie and ' +
    'Nota_numero=:K_Nota_numero and Ent_codigo=:K_ent_codigo and Itnv_Item=:K_itnv_item');
  tq.ParamByName('K_Ped_Numero').AsString := StrZero(P_Ped_numero, 9);
  tq.ParamByName('K_ped_item').AsString := StrZero(P_Ped_Item, 2);
  tq.ParamByName('K_nota_serie').AsString := P_Nota_serie;
  tq.ParamByName('K_Nota_numero').AsString := strzero(P_nota_numero, 9);
  tq.ParamByName('K_ent_codigo').AsString := StrZero(P_ent_codigo, 5);
  tq.ParamByName('K_itnv_item').AsString := StrZero(P_Itnv_item, 2);
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.Params.clear;
    tq.sql.add('Insert into MovItemPedVenda(PED_NUMERO,PED_ITEM,NOTA_SERIE,NOTA_NUMERO,' +
      'ENT_CODIGO,ITNV_ITEM,Ped_Numero2) ' +
      'values(:K_Ped_Numero,' +
      ':K_ped_item,:K_nota_serie,:K_Nota_numero,:K_ent_codigo,:K_itnv_item,:K_Ped_Numero2)');
  end
  else begin
    tq.sql.clear;
    tq.Params.clear;
    tq.sql.add('Update MovItemPedVenda Set Ped_numero=:K_Ped_Numero ,' +
      'Ped_item=:K_ped_item,' +
      'Nota_serie=:K_nota_serie,' +
      'Nota_numero=:K_Nota_numero,' +
      'Ent_codigo=:K_ent_codigo,' +
      'Itnv_Item=:K_itnv_item,' +
      'Ped_Numero2=:k_Ped_Numero2 ' +
      'where Ped_numero=:K_Ped_Numero and Ped_item=:K_ped_item and Nota_serie=:K_nota_serie and ' +
      'Nota_numero=:K_Nota_numero and Ent_codigo=:K_ent_codigo and Itnv_Item=:K_itnv_item');
  end;
  tq.ParamByName('K_Ped_Numero').AsString := StrZero(P_Ped_numero, 9);
  tq.ParamByName('K_ped_item').AsString := StrZero(P_Ped_Item, 2);
  tq.ParamByName('K_nota_serie').AsString := P_Nota_serie;
  tq.ParamByName('K_Nota_numero').AsString := strzero(P_nota_numero, 9);
  tq.ParamByName('K_ent_codigo').AsString := StrZero(P_ent_codigo, 5);
  tq.ParamByName('K_itnv_item').AsString := StrZero(P_Itnv_item, 2);
  tq.ParamByName('k_Ped_Numero2').AsString := StrZero(Ped_Numero2, 9);
  tq.execsql;
  result := 1;
  tq.close;
  tq.Free;
end;

function TMovItemPedVenda.Recuperar(P_Ped_numero, P_Ped_Item, P_Nota_serie,
  P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.close;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where ' +
    'Ped_numero=:K_Ped_Numero and Ped_item=:K_ped_item and Nota_serie=:K_nota_serie and ' +
    'Nota_numero=:K_Nota_numero and Ent_codigo=:K_ent_codigo and Itnv_Item=:K_itnv_item');
  tq.ParamByName('K_Ped_Numero').AsString := StrZero(P_Ped_numero, 9);
  tq.ParamByName('K_ped_item').AsString := StrZero(P_Ped_Item, 2);
  tq.ParamByName('K_nota_serie').AsString := P_Nota_serie;
  tq.ParamByName('K_Nota_numero').AsString := strzero(P_nota_numero, 9);
  tq.ParamByName('K_ent_codigo').AsString := StrZero(P_ent_codigo, 5);
  tq.ParamByName('K_itnv_item').AsString := StrZero(P_Itnv_item, 2);
  tq.open;
  if not tq.eof then begin
    V_Ped_Numero := tq.FieldByName('Ped_numero').AsString;
    V_Ped_item := tq.FieldByName('ped_item').AsString;
    V_Nota_serie := tq.FieldByName('Nota_serie').AsString;
    V_Nota_numero := tq.FieldByName('Nota_numero').AsString;
    V_Ent_codigo := tq.FieldByName('ent_codigo').AsString;
    V_Itnv_item := tq.FieldByName('Itnv_Item').AsString;
    Ped_Numero2 := tq.FieldByName('Ped_Numero2').AsString;
    result := 1;
  end
  else begin
    V_Ped_Numero := '';
    V_Ped_item := '';
    V_Nota_serie := '';
    V_Nota_numero := '';
    V_Ent_codigo := '';
    V_Itnv_item := '';
    Ped_Numero2 := '';
    result := 0;
  end;
  tq.close;
  tq.free;
end;

function TMovItemPedVenda.Recuperar1(var pedido1, Ped_Item: string;
  Ent_Codigo, Nota_Numero, Nota_Serie, Nota_Item: string): Boolean;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('Select * from MovItemPedVenda where Ent_Codigo=:a and Nota_Numero=:b and ' +
    'Nota_Serie=:c and Itnv_item=:d');
  tq.params[0].asstring := strzero(Ent_Codigo, 5);
  tq.params[1].asstring := strzero(Nota_Numero, 9);
  tq.params[2].asstring := Nota_Serie;
  tq.params[3].asstring := strzero(Nota_Item, 2);
  tq.open;
  if tq.eof then result := false else result := true;
  pedido1 := tq.fieldbyname('Ped_Numero').asstring;
  Ped_Item := tq.fieldbyname('Ped_Item').asstring;
  Ped_Numero2 := tq.fieldbyname('Ped_Numero2').asstring;
  tq.next;
  tq.Close;
  tq.Free;
end;

function TMovItemPedVenda.RecuperarNota(P_Nota_serie, P_nota_numero, P_ent_codigo, P_Itnv_item: string): byte;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.close;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where ' +
    'Nota_serie=:K_nota_serie and ' +
    'Nota_numero=:K_Nota_numero and Ent_codigo=:K_ent_codigo and Itnv_Item=:K_itnv_item');
  tq.ParamByName('K_nota_serie').AsString := P_Nota_serie;
  tq.ParamByName('K_Nota_numero').AsString := strzero(P_nota_numero, 9);
  tq.ParamByName('K_ent_codigo').AsString := StrZero(P_ent_codigo, 5);
  tq.ParamByName('K_itnv_item').AsString := StrZero(P_Itnv_item, 2);
  tq.open;
  if not tq.eof then begin
    V_Ped_Numero := tq.FieldByName('Ped_numero').AsString;
    V_Ped_item := tq.FieldByName('ped_item').AsString;
    V_Nota_serie := tq.FieldByName('Nota_serie').AsString;
    V_Nota_numero := tq.FieldByName('Nota_numero').AsString;
    V_Ent_codigo := tq.FieldByName('ent_codigo').AsString;
    V_Itnv_item := tq.FieldByName('Itnv_Item').AsString;
    result := 1;
  end else begin
    V_Ped_Numero := '';
    V_Ped_item := '';
    V_Nota_serie := '';
    V_Nota_numero := '';
    V_Ent_codigo := '';
    V_Itnv_item := '';
    result := 0;
  end;
  tq.close;
  tq.free;
end;

procedure TMovItemPedVenda.Recuperar_Nota;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where Ped_Numero=:a and Ped_Item=:b ORDER by nota_numero,lpad(ped_Item,3,''0'')');
  tq.Params[0].asstring := pedido;
  tq.Params[1].asstring := item;
  tq.open;
  V_Nota_serie := tq.FieldByName('Nota_serie').AsString;
  V_Nota_numero := tq.FieldByName('Nota_numero').AsString;
  V_Ent_codigo := tq.FieldByName('ent_codigo').AsString;
  V_Itnv_item := tq.FieldByName('Itnv_Item').AsString;
  tq.close;
  tq.free;
end;

procedure TMovItemPedVenda.Recuperar_Nota1(pedido, item: string);
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt.Db);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where Ped_Numero=:a and Ped_Item=:b ORDER by nota_numero,lpad(ped_Item,3,''0'')');
  tq.Params[0].asstring := pedido;
  tq.Params[1].asstring := item;
  tq.open;
  V_Nota_serie := '';
  V_Nota_numero := '';
  V_Ent_codigo := '';
  V_Itnv_item := '';
  while not tq.Eof do begin
    if V_Nota_serie = '' then begin
      V_Nota_serie := chr(39) + tq.FieldByName('Nota_serie').AsString + chr(39);
    end else begin
      V_Nota_serie := V_Nota_serie + ',' + chr(39) + tq.FieldByName('Nota_serie').AsString + chr(39);
    end;

    if V_Nota_Numero = '' then begin
      V_Nota_Numero := chr(39) + tq.FieldByName('Nota_Numero').AsString + chr(39);
    end else begin
      V_Nota_numero := V_Nota_Numero + ',' + chr(39) + tq.FieldByName('Nota_Numero').AsString + chr(39);
    end;

    tq.Next;

  end;
  V_Ent_codigo := tq.FieldByName('ent_codigo').AsString;
  V_Itnv_item := tq.FieldByName('Itnv_Item').AsString;
  tq.close;
  tq.free;

end;

function TMovItemPedVenda.Retorna_Pedido;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where Nota_Serie = :a and Nota_Numero = :b and Ent_Codigo = :c');
  tq.Params[0].asstring := p_Nota_Serie;
  tq.Params[1].asstring := p_Nota_Numero;
  tq.Params[2].asstring := p_Ent_Codigo;
  tq.open;
  result := tq.fieldbyname('Ped_Numero').asstring;
  tq.close;
  tq.free;
end;

function TMovItemPedVenda.Retorna_Pedido1(P_bkp_serie, P_bkp_numero, P_ent_codigo: string): string;
var
  tq: TQuery;
  Backup: TBackupVenda;
  Ped, n1, n2: string;
begin
  Backup := TBackupVenda.Create;
  Backup.RecuperarNota(P_bkp_serie, P_bkp_numero, P_ent_codigo, n1, n2);
  Backup.Free;

  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from MovItemPedVenda where Nota_Serie=:a and Nota_Numero=:b and ' +
    'Ent_Codigo=:c');
  tq.Params[0].asstring := P_bkp_serie;
  tq.Params[1].asstring := n1;
  tq.Params[2].asstring := p_Ent_Codigo;
  tq.open;
  Ped := tq.fieldbyname('Ped_Numero').asstring;

  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from BACKUP_PEDIDO where Ped_Numero=:a and Ent_Codigo=:b');
  tq.Params[0].asstring := Ped;
  tq.Params[1].asstring := P_ent_codigo;
  tq.Open;
  Result := tq.FieldByName('Bkp_Pedido').AsString;
  tq.close;
  tq.free;
end;

function TMovItemPedVenda.Retorna_PedidoItem(P_Nota_serie, P_nota_numero,
  P_ent_codigo: string): string;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select * from ItemPedVenda where Nota_Serie=:a and Nota_Numero=:b and ' +
    'Ent_Codigo=:c');
  tq.Params[0].asstring := p_Nota_Serie;
  tq.Params[1].asstring := p_Nota_Numero;
  tq.Params[2].asstring := p_Ent_Codigo;
  tq.open;
  result := tq.fieldbyname('Ped_Numero').asstring;
  tq.close;
  tq.free;
end;

function TMovItemPedVenda.Retorna_Pedidos(P_Nota_serie, P_nota_numero, P_ent_codigo: string): string;
var
  tq: TQuery;
  xNum: string;
  Backup_Pedido: TBackupPedido;
begin
  Backup_Pedido := TBackupPedido.Create;
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select distinct Ped_Numero from MovItemPedVenda where Nota_Serie=:a and Nota_Numero=:b and ' +
    'Ent_Codigo=:c');
  tq.Params[0].asstring := p_Nota_Serie;
  tq.Params[1].asstring := p_Nota_Numero;
  tq.Params[2].asstring := p_Ent_Codigo;
  tq.open;
  xNum := '';

  while not tq.Eof do begin
    if copy(xNum, length(xNum) - 8, 9) <> backup_pedido.RecuperarPedido2(tq.FieldByName('Ped_Numero').AsString) then begin
      if xNum = '' then
        xNum := backup_pedido.RecuperarPedido2(tq.FieldByName('Ped_Numero').AsString)
      else
        xNum := xNum + '/' + backup_pedido.RecuperarPedido2(tq.FieldByName('Ped_Numero').AsString);
    end;
    tq.Next;
  end;

  Result := xNum;
  tq.close;
  tq.free;
  Backup_Pedido.Free;

end;

function TMovItemPedVenda.Retorna_QtdePedidos(P_Nota_serie, P_nota_numero, P_ent_codigo: string): Integer;
var
  tq: TQuery;
begin
  tq := TQuery.create(DmInt);
  tq.DatabaseName := DmInt.Db.DatabaseName;
  tq.active := false;
  tq.sql.clear;
  tq.Params.clear;
  tq.sql.add('Select distinct count(Ped_Numero) as Tot from MovItemPedVenda where Nota_Serie=:a and Nota_Numero=:b and ' +
    'Ent_Codigo=:c');
  tq.Params[0].asstring := p_Nota_Serie;
  tq.Params[1].asstring := p_Nota_Numero;
  tq.Params[2].asstring := p_Ent_Codigo;
  tq.open;
  Result := tq.FieldByName('Tot').AsInteger;
  tq.close;
  tq.free;
end;

end.
